# Created by Katie Cunningham
# This script interfaces with a Trac database to get various reports
# on tickets during a certain date range, for a certain milestone.
# It also returns a summary of all tickets for a chosen milestone.

# Requires pysqlite2

from datetime import datetime
from time import mktime

import sqlite
import os
import sys

from traclib import *

'''# Returns all tickets that have been changed in a certain date range,
# of a certain milestone.
def getChangedTix(cur, start, end, milestone):
	sqlcmd = "select distinct ch.ticket, t.summary from ticket_change ch left join ticket t on (ticket=id) where ch.time > "+str(start)+" and ch.time < "+str(end) + " and milestone = '" + milestone + "'"
	cur.execute(sqlcmd)
	tix = cur.fetchall()
	printTix(tix)

# Returns a summary of how many tickets are in a given milestone, by status.
def getMilestoneOverview(cur, milestone):
	sqlcmd = "select distinct status from ticket"
	cur.execute(sqlcmd)
	statuses = cur.fetchall()
	for status in statuses:
		sqlcmd = "select id from ticket where milestone = '" + milestone + "' and status = '" + status[0] + "'"
		cur.execute(sqlcmd)
		tix = cur.fetchall()
		print status[0], ": ", tix.__len__()

# Converts a timestamp to unixtime
def getUnixTime(dtime):
	utime = int(mktime(dtime.timetuple())+1e-6*dtime.microsecond)
	return utime

# Gets all tickets that were closed within a certain daterange, of
# a certain milestone.
def getClosedTix(cur, start, end, milestone):
        sqlcmd = "select distinct ch.ticket, t.summary from ticket_change ch left join ticket t on (ticket=id) where ch.time > "+str(start)+" and ch.time < "+str(end)+" and ch.newvalue = 'closed' and milestone = '" + milestone + "'"
        cur.execute(sqlcmd)
        tix = cur.fetchall()
        printTix(tix)

# Gets all tickets that were opened within a certain date range, given
# a certain milestone
def getOpenedTix(cur, start, end, milestone):
        sqlcmd = "select distinct ch.ticket, t.summary from ticket_change ch left join ticket t on (ticket=id) where t.time > "+str(start)+" and t.time < "+str(end)+ " and milestone = '" + milestone + "'"
        cur.execute(sqlcmd)
        tix = cur.fetchall()
	printTix(tix)

# Gets all tickets that were moved to testing within a certain 
# date range, given a certain milestone.
def getTestingTix(cur, start, end, milestone):
        sqlcmd = "select distinct ch.ticket, t.summary from ticket_change ch left join ticket t on (ticket=id) where ch.time > "+str(start)+" and ch.time < "+str(end) + " and milestone = '" + milestone + "' and (ch.newvalue like '%verify%' or ch.newvalue = '%test%')"
        cur.execute(sqlcmd)
        tix = cur.fetchall()
        printTix(tix)

# Gets a date from the user
def getTimeFromUser():
	year = int(raw_input("Year: "))
	month = int(raw_input("Month: "))
	day = int(raw_input("Day: "))
	date = str(year)+"-"+str(month)+"-"+str(day)
	return datetime.strptime(date, "%Y-%m-%d")

# An interactive opener for the program. When you actually want input from 
# the user, have this in the __main__ part of the program. The user will
# be asked for two dates and a database name.
def interactive(unixStart, unixEnd, dbname):
        print "Welcome to the ticket change thinger"
        startDate = getTimeFromUser()
        print startDate
        unixStart = getUnixTime(startDate)
        endDate = getTimeFromUser()
        print endDate
        unixEnd = getUnixTime(endDate)
        dbname = raw_input("Database name: ")
	return unixStart, unixEnd, dbname

# An non-interactive opener. Supplies dates and a database.
def nonInteractive(unixStart, unixEnd, dbname):
        print "Welcome to the ticket change thinger"
        unixStart = 1214870400
        unixEnd = 1217548800
        dbname = "july.db"
	return unixStart, unixEnd, dbname

# Prints the reports for all queries.
def printResults(cur, unixStart, unixEnd, milestone):
        print "Tickets changed:"
        getChangedTix(cur, unixStart, unixEnd, milestone)
	print "\n\n\n"
        print "Tickets opened:"
        getOpenedTix(cur, unixStart, unixEnd, milestone)
        print "\n\n\n"
        print "Tickets closed:"
        getClosedTix(cur, unixStart, unixEnd, milestone)
        print "\n\n\n"
        print "Tickets moved to testing:"
        getTestingTix(cur, unixStart, unixEnd, milestone)
        print "\n\n\n"
        print "Overview for current milestone:"	
	getMilestoneOverview(cur, milestone)
	return cur

# Gets all milestones, then has the user choose one. That milestone is
# returned.
def getMilestones(cur):
	sqlcmd = "select distinct milestone from ticket"
	cur.execute(sqlcmd)
	milestones = cur.fetchall()
	milestones.sort()
	for milestone in milestones:
		print str(milestones.index(milestone)), " - ", milestone[0]
	msNum = raw_input("Which milestone (#): ")
	milestone = milestones[int(msNum)]
	milestone = milestone[0]
	return milestone 
	
'''

if __name__ == "__main__":
	unixStart = 0
	unixEnd = 0
	dbname = ""

	unixStart, unixEnd, dbname = interactive(unixStart, unixEnd, dbname)
	con = openDB(dbname)
        cur = con.cursor()
        milestone = getMilestones(cur)
	printResults(cur, unixStart, unixEnd, milestone)
